package com.edu.modules.baseinfo.dao;

import com.edu.modules.baseinfo.model.LessonPaly;
import com.sari.common.persistence.BaseDao;
import com.sari.common.utils.DateUtils;
import org.springframework.stereotype.Repository;

import java.util.List;

/**
 * Created by lab on 2016/7/15.
 */
@Repository
public class StudentDao extends BaseDao {

	public List getStudentList(String name,String grade,String phone,
							   String linkman,int offset,int limit){

		String sql = "select * from t_student where 1=1";
		if(!name.equals("")){
			sql += " and student_name like '%"+name+"%'";
		}
		if(!grade.equals("")){
			sql += " and student_grade like '%"+grade+"%'";
		}
		if (!phone.equals("")){
			sql += " and phone like '%"+phone+"%'";
		}
		if(!linkman.equals("")){
			sql += " and linkman like '%"+linkman+"%'";
		}
		sql +=" ORDER BY create_time DESC limit ?,?";

		return super.listBySql(sql,offset,limit);
	}

	public int getStudentCounts(String name,String grade,String phone,
								String linkman){

		String sql = "select count(*) from t_student where 1=1";
		if(!name.equals("")){
			sql += " and student_name like '%"+name+"%'";
		}
		if(!grade.equals("")){
			sql += " and student_grade like '%"+grade+"%'";
		}
		if (!phone.equals("")){
			sql += " and phone like '%"+phone+"%'";
		}
		if(!linkman.equals("")){
			sql += " and linkman like '%"+linkman+"%'";
		}

		return super.listSqlCount(sql);
	}

	public List getEvaluateList(String studentId){

		String sql = "select c.student_name,c.teacher_name,c.lesson_name,c.content,c.create_time " +
				"from t_student b,t_evaluate c where b.student_name = c.student_name and b.student_id = ?";

		return super.listBySql(sql,studentId);
	}

	public List getLessonEvaluateList(String studentId){

		String sql = "select * from t_lesson_evaluation where student_id = ?";

		return super.listBySql(sql,studentId);
	}

	public LessonPaly getLessonPlay(String studentId) {

		String sql = "SELECT b.play_number,b.play_password FROM t_sign_up a,t_lesson_play b " +
				"WHERE a.lesson_id = b.lesson_id AND a.student_id = ? ORDER BY b.play_id DESC LIMIT 1";

		return (LessonPaly) super.findForObject(sql,LessonPaly.class,studentId);
	}

	public int getStudentCountStatistics(String grade,String lessonName,
	                                 String startDate,String endDate){

		String sql = "SELECT SUM(sign_number) FROM t_lesson WHERE 1=1";
		if(!grade.equals("")){
			sql += " and lesson_class = '"+grade+"'";
		}
		if (!lessonName.equals("")){
			sql += " and lesson_name = '"+lessonName+"'";
		}
		sql +=" AND start_date BETWEEN ? AND ? and start_date <= ?";

		return super.getCount(sql,startDate,endDate,DateUtils.getDate());
	}

	public int getAllStudentCount(){

		String sql = "SELECT SUM(sign_number) FROM t_lesson WHERE start_date <= ?";

		return super.getCount(sql,DateUtils.getDate());
	}
}
